# import pymysql
#
# conn = pymysql.connect(
#     host='localhost',
#     user='root',
#     port=3306,
#     passwd='123',
#     db='hongruan',
#     charset='utf8'
# )
#
# cursor = conn.cursor()
#
# sql = '''
#     CREATE TABLE IF NOT EXISTS person (
#         username VARCHAR(20),
#         age INT,
#         sex CHAR(4),
#         height double(3, 2)
#     )
# '''
# # 创建person表
# cursor.execute(sql)
#
# try:
#     # 循环输入10个人的信息并插入到数据库中
#     for i in range(1, 11):
#         username = input("请输入第{}个人的姓名：".format(i))
#         age = int(input("请输入第{}个人的年龄：".format(i)))
#         sex = input("请输入第{}个人的性别：".format(i))
#         height = input("请输入第{}个人的身高：".format(i))
#
#         cursor.execute("INSERT INTO person (username, age, sex, height) VALUES (%s, %s, %s, %s)",
#                        (username, age, sex, height))
#         conn.commit()
#         print(username, '的个人信息添加成功！')
# except Exception as e:
#     print('个人信息添加失败！', e)
# finally:
#     # 关闭数据库连接
#     cursor.close()
#     conn.close()

from openpyxl import load_workbook
import pymysql
from read_excel import ReadData
from faker import Faker
import random

fake = Faker(locale='zh_CN')


# 创建个人信息
def write_excel():
    s = ['男', '女']
    idcard = fake.pyint(100000000000, 999999999999, 1)
    username = str(fake.user_name())
    pwd = str(fake.password())
    telphone = str(fake.phone_number())
    email = str(fake.email())
    age = str(random.randint(18, 66))
    sex = random.choice(s)
    if sex == '男':
        realname = fake.name_male()
    else:
        realname = fake.name_female()
    address = fake.address()
    hiredata = str(fake.date(pattern='%Y-%m-%d', end_datetime='now'))
    sal = fake.pyint(1000, 99999, 1)
    job = fake.job()
    company = fake.company()
    data = [idcard, username, realname, pwd, telphone, email, age, sex, address, hiredata, sal, job, company]
    return data

# 在Excel表中添加数据
# 打开excel文件
wb = load_workbook('测试.xlsx')
# 打开sheet页
st = wb['openpyxl添加']

# 清空所有数据，重新插入
st.delete_rows(1, st.max_row)
# 插入表头
data1 = ['id', 'idcard', 'username', 'realname', 'pwd', 'telphone', 'email', 'age', 'sex', 'address', 'hiredate', 'sal', 'job', 'company']
st.append(data1)
# 插入500行重复数据
# data2 = [1, '12434', '张三', 'z12', '123', '158741235', '212121@qq.com', 15, '男', '中国', '2001-10-12', 9546, '人事', '传媒']
for rows in range(1, 501):
    data2 = [rows]
    data2.extend(write_excel())
    # print(data2)
    st.append(data2)

# 保存excel文件
wb.save('测试.xlsx')
print('数据已成功插入excel文件！')

# 从excel表格中获取数据，并传入mysql
new_data = ReadData.read_excel('测试.xlsx', 'openpyxl添加')
print('已成功从excel中获取数据，开始上传mysql...')

# 建立数据库连接
con = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    passwd='123',
    db='hongruan',
    charset='utf8'
)

# 创建游标
cur = con.cursor()

# 如果该表已经存在，则先删除表
cur.execute('drop table if exists user_info')

# 创建表
cur.execute("""
create table user_info(
    id int(11),
    idcard varchar(50),
    username varchar(50),
    realname varchar(50),
    pwd varchar(50),
    telphone varchar(12),
    email varchar(100),
    age int(11),
    sex varchar(20),
    address varchar(200),
    hiredate date,
    sal double(9,2),
    job varchar(100),
    company varchar(100)

)
""")
print('数据表创建成功！')

# 将从excel表中获取的数据传到mysql中
sql = 'insert into user_info values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
cur.executemany(sql, new_data[1:])
con.commit()
cur.close()
con.close()
print('数据上传完成!')
